const express = require('express');
const router = express.Router();
// 引入响应信息class类
const Message = require('../utils/message')
// 引入mysql连接池
const pool = require('../utils/connections')

router.get('/findAll', (req, res) => {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    const sql = 'select * from tab_student;'
    connection.query(sql, function (error, results) {
      if (error) throw error;
      res.json(new Message(200, '查询成功', results))
      connection.release();
    });
  })
})

router.get('/pageQuery', (req, res) => {
  let page = req.query.page
  let pageSize = req.query.pageSize
  const sql = `select * from tab_student limit ${(page - 1) * pageSize},${pageSize};`
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query(sql, (error, results) => {
      if (error) throw error;
      res.json(new Message(200, '查询成功', results))
      connection.release();
    })
  })
})

router.post('/saveOrUpdate', (req, res) => {
  let stu = req.body
  let sql = `insert into tab_student(name,gender,birth) values('${stu.name}','${stu.gender}',${stu.birth});`
  if (stu.id) {
    sql = `update tab_student set name='${stu.name}',gender='${stu.gender}',birth=${stu.birth} where id = ${stu.id};`
  }
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query(sql, (error, results) => {
      if (error) throw error;
      res.json(new Message(200, '操作成功'))
      connection.release();
    })
  })
})

router.delete('/deleteById', (req, res) => {
  const id = req.query.id
  const sql = `delete from tab_student where id = ${id};`
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query(sql, (error) => {
      if (error) throw error;
      res.json(new Message(200, '删除成功'));
      connection.release();
    })
  })
})

module.exports = router